/**
 * Copyright 2017 asiainfo Inc.
 **/
package net.blogways.ordermgr.entity;

import com.asiainfo.bpc.utils.ToolFactory;
import net.blogways.ordermgr.common.service.CommonService;
import net.blogways.ordermgr.util.Constants;
import net.blogways.ordermgr.util.DBTools;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.net.URLDecoder;
import java.util.*;


/**
 * Created by chenfan on 2017/7/13.
 */
public class Datatable {

  private final Logger log = LoggerFactory.getLogger(getClass());

  private CommonService commonService = (CommonService) ToolFactory.getInstance().getContext()
      .getBean("commonService");

  /**
   * select datatable.
   *
   * @param sql
   * @param sqlCount
   * @param context
   * @return
   * @throws Exception
   */
  public Map datatable(String sql, String sqlCount, Map<String, Object> context)
      throws Exception {
    Map base = (Map) context.get("Base");
    Long draw = Long.valueOf(context.get("draw").toString());
    List columns = (List) context.get("columns");
    Long start = Long.valueOf(context.get("start").toString());
    Long length = Long.valueOf(context.get("length").toString());
    Map search = (Map) context.get("search");
    List<Map> order = new ArrayList();
    order = context.containsKey("order") ? (List<Map>) context.get("order") : order;
    Map<String, Object> conditions = new HashMap<>();
    conditions = context.containsKey("conditions") ? (Map) context.get("conditions") : conditions;
    List<Map<String, Object>> comparelist = new ArrayList<Map<String, Object>>();
    comparelist = context.containsKey("comparelist") ?
        (List<Map<String, Object>>) context.get("comparelist") : comparelist;
    String tableName = (String) context.get("table_name");
    String operateCode = base.get("OPERATE_CODE") == null ? "" : base.get("OPERATE_CODE").toString();
    String dbRoute;
    if (context.containsKey("dbRoute") && !"".equals(context.get("dbRoute"))) {
      dbRoute = (String) context.get("dbRoute");
    } else {
      dbRoute = Constants.DATABASE_CEN;
    }
    Map paramsTable = context.containsKey("paramsTable") ? (Map) context.get("paramsTable") : new HashMap();
    List orArr = new ArrayList();
    List andArr = new ArrayList();


    Map<String, String> param = new HashMap<String, String>();
    param.put("table_name", tableName);
    param.putAll(paramsTable);
    log.debug("params" + param);


    //  获取sql语句
    String strSql = (String) base.get("SUPER_SQL");
    if (context.get("toSql") != null && context.get("toSql").toString().equals("1")) {
      if (strSql != null && !"".equals(strSql)) {
        List superRes = ToolFactory.getInstance().getSqlCodeService(dbRoute).select(sql, param);
        String[] superSqlArr = strSql.split(",");
        if (superSqlArr.length != 3) {
          throw new RuntimeException("SUPER_SQL格式不正确");
        }
        String tabName = superSqlArr[0];
        String tabRef = superSqlArr[1];
        String superRoute = superSqlArr[2];
        Map tabParams = new HashMap();
        tabParams.put("tabName", tabName);
        if (param.get("VAPN_ID") != null || param.get("VAPN_NAME") != null) {
          tabParams.put("sqlRef", tabRef + "_APN");
        } else {
          tabParams.put("sqlRef", tabRef);
        }
        String parsedSql;
        List sqlRs = ToolFactory
            .getInstance()
            .getSqlCodeService("cen")
            .select("SELECT SQL_STMT FROM code_code WHERE tab_name= :tabName AND sql_ref = :sqlRef", tabParams);
        if (sqlRs.size() > 0) {
          parsedSql = (String) ((Map) sqlRs.get(0)).get("SQL_STMT");
        } else {
          throw new RuntimeException("SQL语句不存在.TAB_NAME:" + tabName + ",SQL_REF:" + tabRef);
        }
        if (superRes != null) {
          int i = 0;
          String inSql = "";
          for (Object row : superRes) {

            Map<String, Object> mapRow = (Map) row;
            Map paramRows = new HashMap();
            if (i == 0) {
              for (String m : mapRow.keySet()) {
                if (DBTools.sql(mapRow.get(m).toString())) {
                  inSql += " OR " + m + " in (" + mapRow.get(m).toString();
                } else {
                  Map error = new HashMap();
                  error.put("ERROR", 0);
                  return error;
                }

              }
            }
            for (String m : mapRow.keySet()) {
              if (DBTools.sql(mapRow.get(m).toString())) {
                inSql += "," + mapRow.get(m).toString();
              } else {
                Map error = new HashMap();
                error.put("ERROR", 0);
                return error;
              }
            }
            i++;
          }
          if (superRes.size() > 0) {
            inSql += ") ";
          }
          sql = parsedSql + inSql + ')';
          sqlCount = sql.replaceAll("(?i)^select[\\s\\S]*?from\\s+?", "select count(*) from ");
          dbRoute = superRoute;
        }
      }
    }

    for (int i = 0; i < columns.size(); i++) {
      Map column = (Map) columns.get(i);
      if (!DBTools.sql(column.get("data").toString())) {
        Map error = new HashMap();
        error.put("ERROR", 0);
        return error;
      }
      String searchValue = (String) search.get("value");
      String advancedSearchValue = (String) ((Map) column.get("search")).get("value");

      if (!DBTools.sql(((Map) column.get("search")).get("regex").toString())) {
        Map error = new HashMap();
        error.put("ERROR", 0);
        return error;
      }
      if ((Boolean) column.get("searchable")) {
        //  搜索
        if (!StringUtils.isBlank(searchValue)) {

          //如果编码,先解码
          if (searchValue.contains("coded`")) {
            if (searchValue.split("`").length > 1) {
              searchValue = URLDecoder.decode(searchValue.split("`")[1], "UTF-8");
            } else {
              searchValue = "";
            }
          }
          if (DBTools.sql(column.get("name").toString())&& DBTools.sql(searchValue)) {
            orArr.add((String) column.get("name") + " LIKE binary \"%"
              + searchValue + "%\" ");
          } else {
            Map error = new HashMap();
            error.put("ERROR", 0);
            return error;
          }
        }
        //  高级搜索
        if (!StringUtils.isBlank(advancedSearchValue)) {
          //如果编码,先解码
          if (advancedSearchValue.contains("coded`")) {
            if (advancedSearchValue.split("`").length > 1) {
              advancedSearchValue = URLDecoder.decode(advancedSearchValue.split("`")[1], "UTF-8");
            } else {
              advancedSearchValue = "";
            }
          }

          if (advancedSearchValue.indexOf("`_`") > -1) {
            String[] valueArrStr = (String[]) advancedSearchValue.split("`_`");
            ArrayList valueArr = (ArrayList) Arrays.asList(valueArrStr);

            if ((String) valueArr.get(0) != "") {
              if (DBTools.sql(column.get("name").toString())&&  DBTools.sql((String) valueArr.get(0))) {
                andArr.add((String) column.get("name") + ">= \"" + (String) valueArr.get(0) + "\" ");
              } else {
                Map error = new HashMap();
                error.put("ERROR", 0);
                return error;
              }
            }
            if ((String) valueArr.get(1) != "") {
              if (DBTools.sql(column.get("name").toString())&&  DBTools.sql((String) valueArr.get(1))) {
                andArr.add((String) column.get("name") + ">= \"" + (String) valueArr.get(1) + "\" ");
              } else {
                Map error = new HashMap();
                error.put("ERROR", 0);
                return error;
              }
            }
          } else {
              String name = (String) column.get("name");
              int index = name.indexOf("|");
              if (!DBTools.sql(advancedSearchValue)) {
                Map returnMap = new HashMap();
                returnMap.put("ERROR", 0);
                return returnMap;
              }
            if (index > -1) {
              String t1 = name.substring(0, index);
              String t2 = name.substring(index + 1);
              if (!DBTools.sql(t1)) {
                Map error = new HashMap();
                error.put("ERROR", 0);
                return error;
              }
              if (t2.equals("1")) {
                andArr.add(t1 + " =:V" + t1);
                param.put("V" + t1, advancedSearchValue);
              } else {
                andArr.add((String) column.get("name") + " LIKE binary \"%" +
                        advancedSearchValue + "%\" ");
              }
            } else {
              if (DBTools.sql(column.get("name").toString())&&  DBTools.sql(advancedSearchValue)) {
                if (column.get("name").equals("A.STATUS")){
                  andArr.add((String) column.get("name") + " = " + advancedSearchValue);
                } else {
                  andArr.add((String) column.get("name") + " LIKE binary \"%" +
                          advancedSearchValue + "%\" ");
                }
              } else {
                Map error = new HashMap();
                error.put("ERROR", 0);
                return error;
              }
            }

          }
        }
      }
    }
    String orArrStr = new String();
    String andArrStr = new String();
    String where = new String();
    String conditonStr = new String();
    String comparelistStr = "";
    if (orArr.size() > 0 || andArr.size() > 0) {
      if (orArr.size() > 0) {
        for (int i = 0; i < orArr.size(); i++) {
          orArrStr += ((String) orArr.get(i)) + " OR ";
        }
        orArrStr = orArrStr.substring(0, orArrStr.length() - 3);
      }
      if (andArr.size() > 0) {
        for (int i = 0; i < andArr.size(); i++) {
          andArrStr += ((String) andArr.get(i)) + " AND ";
        }
        andArrStr = andArrStr.substring(0, andArrStr.length() - 4);
      }
    }



    if (orArr.size() > 0 && andArr.size() > 0) {
      where += " and " + andArrStr + " and " + "(" + orArrStr + ")";
    } else if (orArr.size() > 0 || andArr.size() > 0) {
      where += " and " + "(" + andArrStr + orArrStr + ")";
    }
    log.debug("where=" + where);

    Object sumFee = 0.0;
    Object sumCommission = 0.0;
    if("7005".equals(operateCode)){
      if(!conditions.isEmpty()){
        StringBuffer conditionsTemp = new StringBuffer();
        for(Map.Entry<String,Object> entry:conditions.entrySet()){
          String key = entry.getKey();
          String value = (String) entry.getValue();
          switch (key){
            case "startMSISDN":
              conditionsTemp.append(" and b.MSISDN >= :startMSISDN");
              break;
            case "endMSISDN":
              conditionsTemp.append(" and b.MSISDN <= :endMSISDN");
              break;
            case "startICCID":
              conditionsTemp.append(" and a.GOODS_CODE >= :startICCID");
              break;
            case "endICCID":
              conditionsTemp.append(" and a.GOODS_CODE <= :endICCID");
              break;
            case "startDate":
              conditionsTemp.append(" and a.PAY_TIME >= :startDate");
              break;
            case "endDate":
              conditionsTemp.append(" and a.PAY_TIME < DATE_ADD(STR_TO_DATE(:endDate, '%Y-%m-%d'),INTERVAL 1 DAY) ");
              break;
            case "currmonth":
              conditionsTemp.append(" and DATE_FORMAT( a.PAY_TIME, '%Y-%m') =  DATE_FORMAT( NOW(), '%Y-%m')");
              break;
            case "somemonth":
              conditionsTemp.append(" and DATE_FORMAT( a.PAY_TIME, '%Y%m') = :somemonth");
              break;
            default:
              if(value!=null){
                conditionsTemp.append(" and ").append(key)
                    .append("=")
                    .append(" :").append(key.replaceAll("\\w\\.",""));
              }else {
                conditions.remove(key);
              }
          }
          param.put(key.replaceAll("\\w\\.",""),value);
        }
        conditonStr += conditionsTemp;
      }
      StringBuffer totalFeeSql = new StringBuffer("select sum(ifnull(g.ADJUST_MONEY,0)) as sumFee , sum(ifnull(d.ADJUST_MONEY,0)) as sumCommission from tf_f_pay_order a\n")
          .append("left join tf_f_goods_attr b on a.GOODS_ID = b.GOODS_ID\n")
          .append("left join tf_l_api_callrecord f on a.PAY_ID = f.RECORD_ID\n")
          .append("left join tf_f_acct_goods c on a.GOODS_ID = c.GOODS_ID\n")
          .append("left join tf_f_acct_fee_change d on a.PAY_ID = d.SETTLE_ID\nand c.ACCT_ID = d.ACCT_ID\nand d.FEE_TYPE = 1\n")
          .append("left join tf_f_acct_fee_change g on a.PAY_ID = g.SETTLE_ID\nand c.ACCT_ID = g.ACCT_ID\nand g.FEE_TYPE = 2\nwhere 1=1 ")
          .append(conditonStr)
          .append(where);
      List<Map<String,Object>> lt1 =  ToolFactory.getInstance().getSqlCodeService(dbRoute).select(totalFeeSql.toString(),param);
      if(lt1!=null&&!lt1.isEmpty()){
        sumFee = lt1.get(0).get("sumFee");
        sumCommission = lt1.get(0).get("sumCommission");
      }
    }

    if("7006".equals(operateCode)){
      if(!conditions.isEmpty()){
        StringBuffer conditionsTemp = new StringBuffer();
        for(Map.Entry<String,Object> entry:conditions.entrySet()){
          String key = entry.getKey();
          String value = (String) entry.getValue();
          switch (key){
            case "startDate":
              conditionsTemp.append(" and B.PAY_TIME >= :startDate");
              break;
            case "endDate":
              conditionsTemp.append(" and B.PAY_TIME < DATE_ADD(STR_TO_DATE(:endDate, '%Y-%m-%d'),INTERVAL 1 DAY) ");
              break;
            default:
              if(value!=null){
                conditionsTemp.append(" and ").append(key)
                    .append("=")
                    .append(" :").append(key.replaceAll("\\w\\.",""));
              }else {
                conditions.remove(key);
              }
          }
          param.put(key.replaceAll("\\w\\.",""),value);
        }
        conditonStr += conditionsTemp;
      }

    }

    if ("2010".equals(operateCode)){
      String userName = base.get("USER_NAME").toString();
      String userType = base.get("USER_TYPE").toString();
      StringBuffer conditionsTemp = new StringBuffer();
      param.put("userName", userName);
      switch (userType){
        case "0":
          conditionsTemp.append(" and DIRECTOR = :userName ");
          break;
        case "1":
          conditionsTemp.append(" and CREATE_STAFF_ID = :userName ");
          break;
        case "2":
          conditionsTemp.append(" and MERCHANDISER = :userName and STATE not in ('0', '1', '2') ");
          break;
      }
      conditonStr += conditionsTemp;
    }


    if("1006".equals(operateCode)){
      String ICCIDCondition = "";
      for (String key :  ((Map<String,Object>)paramsTable).keySet()) {
        if (DBTools.sql(paramsTable.get(key).toString()) && DBTools.sql(key)) {
          if ("MIN_ICCID".equals(key)) {
            ICCIDCondition += (" and (A.GOODS_CODE >= '" + paramsTable.get(key).toString() + "'");
          } else if ("MAX_ICCID".equals(key)) {
            ICCIDCondition += (" and A.GOODS_CODE <= '" + paramsTable.get(key).toString() + "')");
          } else if ("MIN_MSISDN".equals(key)) {
            ICCIDCondition += (" and (A.MSISDN >= '" + paramsTable.get(key).toString() + "'");
          } else if ("MAX_MSISDN".contains(key)){
            ICCIDCondition += (" and A.MSISDN <= '" + paramsTable.get(key).toString() + "')");
          }
//          else if("GOODS_CATEGORY_ID".contains(key)){
//            if(!where.contains("GOODS_CATEGORY_ID")){
//              if(!StringUtils.isEmpty((String)paramsTable.get("GOODS_CATEGORY_ID"))) {
//                where += (" and (B.GOODS_CATEGORY_ID =:GOODS_CATEGORY_ID )");
//                log.debug("where = "+where);
//              }
//            }
//          }
        } else {
          Map error = new HashMap();
          error.put("ERROR", 0);
          return error;
        }
      }

      conditonStr += ICCIDCondition;
    }

    if ("1006".equals(operateCode) && !conditions.isEmpty()) {
      String ICCIDCondition = "";
      for (String key : conditions.keySet()) {
        if (DBTools.sql(conditions.get(key).toString()) && DBTools.sql(key)) {
          if ("MIN_ICCID".equals(key)) {
            ICCIDCondition += (" and (A.GOODS_CODE >= '" + conditions.get(key).toString() + "'");
          } else if ("MAX_ICCID".equals(key)) {
            ICCIDCondition += (" and A.GOODS_CODE <= '" + conditions.get(key).toString() + "')");
          } else {
            conditonStr += (" and " + key + " = '" + conditions.get(key).toString() + "'");
          }
        } else {
          Map error = new HashMap();
          error.put("ERROR", 0);
          return error;
        }
      }
      if (!conditions.containsKey("MIN_ICCID") || !conditions.containsKey("MAX_ICCID")) {
        ICCIDCondition = "";
      }
      conditonStr += ICCIDCondition;
      conditions.clear();
    }
    if (!conditions.isEmpty()&&!"7005".equals(operateCode)&&!"7006".equals(operateCode)) {
      for (String key : conditions.keySet()) {
        if (DBTools.sql(conditions.get(key).toString())) {
          conditonStr += " and " + key + " = CONCAT(\"" + conditions.get(key) + "\", '' ) ";
        } else {
          Map error = new HashMap();
          error.put("ERROR", 0);
          return error;
        }
      }
    }
    log.debug("operateCode={}", operateCode);
    log.debug("conditionStr={}", conditonStr);
    // 拼符号
    if (comparelist.size() != 0) {
      for (Map<String, Object> map : comparelist) {
        String symbol = (String) map.get("SYMBOL");
//        symbol = SymbolTool.translate(symbol);
        String sign = " " + symbol + " ";
        comparelistStr += " and :VLEFT" + sign + ":VRIGHT";
        param.put("VLEFT", (String) map.get("LEFT"));
        param.put("VRIGHT", (String) map.get("RIGHT"));
      }
    }
    log.debug("---comparelistStr = " + comparelistStr);
    //  排序
    String sort = new String();
    if (order.size() > 0) {
      for (int i = 0; i < order.size(); i++) {
        String dir = ((Map) order.get(i)).get("dir").toString();
        if (!DBTools.sql(dir)) {
          Map error = new HashMap();
          error.put("ERROR", 0);
          return error;
        }
        Long col = Long.valueOf (((Map) order.get(i)).get("column").toString());
        String dirSet = "asc".equals(dir) ? "asc" : "desc";
        if (!DBTools.sql(((Map) columns.get(col.intValue())).get("orderable").toString())) {
          Map error = new HashMap();
          error.put("ERROR", 0);
          return error;
        }
        if ((Boolean) ((Map) columns.get(col.intValue())).get("orderable")) {
          if (col != null) {
            sort = " order by ";
            String colName;
            String name = (String) ((Map) columns.get(col.intValue())).get("name");
            int index = name.indexOf("|");
            if (index > -1) {
              colName = name.substring(0, index);
            } else {
              colName = name;
            }

            if ("1104".equals(operateCode) || "1102".equals(operateCode)) {
              sort += " BEGIN_DATE " + dirSet + ",BEGIN_TIME " + dirSet;
            } else {
              sort += colName + " " + dirSet;
            }
          }
        }
      }
    }
    log.debug("sort=" + sort);
    // 分页
    String limit = new String();
    if (length != -1) {
      limit = " limit " + start + " ," + length;
    }
    String sqlFilter = "";
    String sqlTotal = "";

    sql = sql + conditonStr + comparelistStr + where + sort + limit;
    sqlFilter = sqlCount + conditonStr + where;
    sqlTotal = sqlCount + conditonStr;


    //支持sql中传递表名
    if (sql.contains(Constants.CDR_TABLE_NAME) && param.containsKey(Constants.TABLE_NAME_CDR)) {
      String cdrTableName = param.get(Constants.TABLE_NAME_CDR);
      sql = sql.replace(Constants.CDR_TABLE_NAME, cdrTableName);
      sqlTotal = sqlTotal.replace(Constants.CDR_TABLE_NAME, cdrTableName);
      sqlFilter = sqlFilter.replace(Constants.CDR_TABLE_NAME, cdrTableName);
    }
    //不可见字符过滤
    sql = filterInvisibleCharacter(sql);
    sqlTotal = filterInvisibleCharacter(sqlTotal);
    sqlFilter = filterInvisibleCharacter(sqlFilter);

    log.debug("sql=" + sql);

    List result = ToolFactory.getInstance().getSqlCodeService(dbRoute).select(sql, param);
    List filterCnt = ToolFactory.getInstance().getSqlCodeService(dbRoute)
        .select(sqlTotal, param);
    List totalCnt = ToolFactory.getInstance().getSqlCodeService(dbRoute)
        .select(sqlFilter, param);
    log.debug("result=" + result);
    log.debug("sqlFilter=" + sqlFilter);
    log.debug("filterCnt=" + filterCnt);

    if("7005".equals(operateCode)&&result!=null&&!result.isEmpty()){
      Map<String,Object> row1 = (Map<String, Object>) result.get(0);
      row1.put("sumFee",sumFee);
      row1.put("sumCommission",sumCommission);
    }

    // 获取 code_to_name
    String strCode2Name = (String) context.get("CODE_TO_NAME");
    log.debug("code2name=" + strCode2Name);

    Map<String, String> mapCode2Name = new HashMap();

    if (strCode2Name != null && !"".equals(strCode2Name)) {

      // 解析 code_to_name
      // 1.code:table_name,type_cdoe:type_name[:intl_code] | ...
      // 2.code:table_name,type_code:type_name:dict_code[:intl_code] | ...
      String[] code2NameArr = strCode2Name.split("\\|");
      for (String strCode : code2NameArr) {

        String key = strCode.split(":")[0];
        String val = strCode.replaceAll(",", ":");

        mapCode2Name.put(key, val);
      }

      log.debug("parse code2name=" + mapCode2Name.toString());

      // 遍历结果集
      if (result != null) {
        for (Object row : result) {
          Map<String, Object> mapRow = (Map) row;
          Map<String, Object> mapTmp = new HashMap<>();

          for (String key : mapRow.keySet()) {
            String v = mapCode2Name.get(key);
            if (v != null) {
              String[] vArr = v.split(":");
              String tabName = vArr[1];
              String typeName = vArr[3];
              String code;
              String intlCode;
              String code2Name;
              Map type2Name;
              String typeNameStr = typeName;
              String route;
              if ("TD_S_DATA_DICT".equals(tabName.toUpperCase())) {
                code = mapRow.get(key).toString();
                String dictCode = vArr[4];
                intlCode = vArr.length == 6 ? vArr[5] : "0";

                code2Name = commonService.dictCode2Name(dictCode, code, intlCode);
                mapTmp.put(typeNameStr, code2Name);
              } else {
                code = vArr[2] + ":" + mapRow.get(key);
                typeNameStr = vArr.length >= 5 ? vArr[4] : typeName;
                String[] typeNameStrArr = typeName.split("\\&");
                String typeNameSend = new String();
                for (int i = 0; i < typeNameStrArr.length; i++) {
                  if (i == 0) {
                    typeNameSend += typeNameStrArr[i];
                    continue;
                  }
                  typeNameSend += "," + typeNameStrArr[i];
                }
                route = vArr.length >= 6 ? vArr[5] : "";
                intlCode = vArr.length == 7 ? vArr[6] : "";

                if (typeNameStrArr.length > 1) {
                  type2Name = commonService.getTypeNameByCode(tabName, code, typeNameSend, intlCode, route);
                  mapTmp = type2Name;
                } else {
                  type2Name = commonService.getTypeNameByCode(tabName, code, typeNameSend, intlCode, route);
                  mapTmp.putAll(type2Name);
                }
              }
            }
          }

          if (!mapTmp.isEmpty()) {
            log.debug("convert code2name=" + mapTmp);
            mapRow.putAll(mapTmp);
          }
        }
      }
    }
    log.debug("result after append codetoname=" + result);
    // 发送结果
    Map sendData = new HashMap();
    sendData.put("data", result);
    sendData.put("recordsTotal", ((Map) filterCnt.get(0)).get("count(*)") == null ?
        ((Map) filterCnt.get(0)).get("COUNT0") : ((Map) filterCnt.get(0)).get("count(*)"));
    if ("9100".equals(operateCode)) {
      sendData.put("recordsFiltered", totalCnt.size());
    } else {
      sendData.put("recordsFiltered", ((Map) totalCnt.get(0)).get("count(*)") == null ?
              ((Map) totalCnt.get(0)).get("COUNT0") : ((Map) totalCnt.get(0)).get("count(*)"));
    }
    sendData.put("draw", draw);
    log.debug("sendData=" + sendData);
    return sendData;
  }

  /**
   * 防止sql注入.
   *
   * @param str
   * @return
   */
  public static boolean sql(String str) throws Exception {
    String reg = "(?i).*([';]+|(--)+|OR\\s+|and|exec|insert|select|delete|updatecount|\\+|trim|%|chr|mid|master|truncate|char|declare|\'|\"\\)|\\\'|\\\").*";
    if (str.matches(reg)) {
      return false;
    }
    return true;
  }

  /**
   * 批量更新详情页SQL处理.
   *
   * @param sql
   * @param sqlTotal
   * @param sqlFilter
   * @param dbRoute
   * @param param
   * @return
   */
  private static Map<String, String> batchOperation(String sql, String sqlTotal,
                                                    String sqlFilter, String dbRoute, Map param) {
    Map<String, String> sqlMap = new HashMap();
    Integer byInder = sql.indexOf("BY");
    Integer limitIndexOfSql = sql.indexOf("LIMIT");
    String limitCondition = sql;
    if (limitIndexOfSql != -1) {
      limitCondition = sql.substring(limitIndexOfSql, sql.length());
    }
    String conditionSql = sql;
    if (byInder != -1) {
      conditionSql = sql.substring(0, byInder + 2) + " BUSINESS_ID " + limitCondition;
    }
    List result = ToolFactory.getInstance().getSqlCodeService(dbRoute).select(conditionSql, param);
    String condition = "";
    if (null != result && !result.isEmpty()) {
      for (int i = 0; i < result.size(); i++) {
        Map conditions = (Map) result.get(i);
        String businessId = (null != conditions.get("BUSINESS_ID"))
            ? conditions.get("BUSINESS_ID").toString() : "";
        condition = condition + "\'" + businessId + "\'" + ",";
      }
    }
    Integer groupIndexOfSql = sql.indexOf("GROUP");
    sql = sql.substring(0, limitIndexOfSql);
    Integer lastIndex = condition.lastIndexOf(",");
    if (lastIndex != -1) {
      condition = condition.substring(0, lastIndex);
    }
    if (!StringUtils.isEmpty(condition)) {
      sql = (new StringBuffer(sql).insert(groupIndexOfSql,
          " AND A.BUSINESS_ID IN (" + condition + ")")).toString();
    }
    Integer groupIndex = sqlTotal.indexOf("GROUP");
    Integer limitIndex = sqlTotal.indexOf("LIMIT");
    String distinct = " BUSINESS_ID";
    if (groupIndex != -1) {
      sqlTotal = (sqlTotal.substring(0, groupIndex));
      sqlFilter = (sqlFilter.substring(0, groupIndex));
    }
    if (limitIndex != -1) {
      sqlTotal = sqlTotal.substring(0, limitIndex);
      sqlFilter = sqlFilter.substring(0, limitIndex);
    }
    sqlTotal = sqlTotal.replace("*", "DISTINCT " + distinct);
    sqlFilter = sqlFilter.replace("*", "DISTINCT " + distinct);

    sqlMap.put("sql", sql);
    sqlMap.put("sqlTotal", sqlTotal);
    sqlMap.put("sqlFilter", sqlFilter);
    return sqlMap;
  }

  /**
   * sql语句中不可见字符过滤
   *
   * @param sql
   * @return String
   */
  private String filterInvisibleCharacter(String sql) {
    String target = "\u200c";
    String replacement = "";
    return sql.replaceAll(target, replacement);
  }


}
